package com.netwit.common.mapper;

import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.google.common.base.CaseFormat;

import org.apache.ibatis.jdbc.SQL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

public class SqlProvider {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 构造查询语句
     * 
     * @param map 包含如下参数:
     * tableName: 表名
     * fields: 列名，以逗号(,)分隔，insert和update一定要有，select没有时默认为 *, 例 user_name,user_code
     * where|whr: whr一般用于固定的查询参数，where在前端可根据查询条件进行动态修改
     *   详细用法：
     *     格式：[whr|where]=col1-val1,(col2~val2;col2*val3;col2^val4)
     *            col和val中间的-代表=，~代表！=，*代表like，^代表not like(在地址栏中输入时需用%5E)
     *            条件之间的关系：,代表and，;代表or
     *            如上述的参数可生成: col1 = val1 and (col2!=val2 or col2 like %val3% or col2 not like %val4%)
     * limit: mysql中分页查询
     * orderBy: 排序，colName[+]升序，colName-降序
     * type: 没有或为空时生成正常的SQL，ONE - limit为"limit 1"，COUNT - fields为"count(1) as rowcount"
     * groupBy: 暂未实现 having: 暂不实现
     * 其他从request中传入的值
     *
     * @return
     */
    public String selectSql(Map<String, Object> map) {
        String tableName = (String) map.get("tableName");
        if (StringUtils.isEmpty(tableName))
            return "select 'table name is invalid.' as message";

        String type = (String) map.get("type");
        boolean isOne = !StringUtils.isEmpty(type) && "ONE".equalsIgnoreCase(type);
        boolean isCount = !StringUtils.isEmpty(type) && "COUNT".equalsIgnoreCase(type);

        String fields = (String) map.get("fields");
        fields = StringUtils.isEmpty(fields) ? "*" : fields;
        fields = isCount ? "count(1) as rowCount" : fields;

        transParamsMap(map);

        SQL sql = new SQL().FROM(tableName).SELECT(fields);
        String conditions = (String) map.get("conditions");
        if (!StringUtils.isEmpty(conditions)) {
            sql.WHERE(conditions);
        }
        String orderBy = (String) map.get("orderBy");
        orderBy = StringUtils.isEmpty(orderBy) ? (String) map.get("orderby") : orderBy;
        if (!StringUtils.isEmpty(orderBy)) {
            sql.ORDER_BY(orderBy);
        }

        String selectSql = sql.toString().replaceAll("[\r\n]", " ");

        if (!isCount) {
            if (isOne) {
                // 只查找一条记录
                selectSql += " limit 1";
            } else {
                // 有分页
                if (map.containsKey("limit")) {
                    selectSql += " " + map.get("limit");
                }
            }
        }

        logger.info(selectSql);

        return selectSql;
    }

    private void getFields(Map<String, String> fields, Object entity) {
        Field[] fs = entity.getClass().getDeclaredFields();
        for (Field f : fs) {
            f.setAccessible(true);
            try {
                if (f.get(entity) != null) {
                    String fName = f.getName();
                    fields.put(fName, "#{entity." + fName + "}");
                    // fields.put(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fName), "#{entity." + fName + "}");
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @param map 为 Mapper 中相应方法的参数的打包, 其中一个为表名(key=tableName),
     *            一个为实体实例(key=entity)或RequestBody(key=requestBody,优先获取)
     * @return insert sql 语句, 适用于 mysql
     */
    @SuppressWarnings("unchecked")
    public String insertSql(Map<String, Object> map) {
        String tableName = (String) map.get("tableName");

        SQL sql = new SQL().INSERT_INTO(tableName);
        Map<String, String> fields = new LinkedHashMap<>();
        if (map.containsKey("requestBody")) {
            Map<String, Object> rb = (Map<String, Object>) map.get("requestBody");
            for (Map.Entry<String, Object> item : rb.entrySet()) {
                String field = item.getKey();
                if (ignoreField(field)) continue;
                fields.put(field, "#{requestBody." + field + "}");
                // fields.put(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field), "#{requestBody." + field + "}");
            }
        } else if (map.containsKey("entity")) {
            Object entity = map.get("entity");
            getFields(fields, entity);
        }

        for (Map.Entry<String, String> m : fields.entrySet()) {
            sql.VALUES(m.getKey(), m.getValue());
        }

        String insertSql = sql.toString().replaceAll("[\r\n]", " ");
        logger.info(insertSql);

        return insertSql;
    }

    /**
     * 根据传入的参数键值对动态生成UPDATE SQL，其中必须包含表名key=tableName所对应的值，一般在URL中传入，
     * 键值中，以 op_,btn 开头的参数将被忽略，不参与 SQL 的构造, 以 whr-，where- 开头的参数将用于 WHERE
     * 子句的构造，这些参数的格式和含义参见selectSql方法的说明
     * 
     * @param map
     * @return
     */
    @SuppressWarnings("unchecked")
    public String updateSql(Map<String, Object> map) {
        String tableName = (String) map.get("tableName");

        Map<String, String> fields = new LinkedHashMap<>();
        String where = "";
        if (map.containsKey("requestBody")) {
            Map<String, Object> rb = (Map<String, Object>) map.get("requestBody");
            transParamsMap(rb);
            for (Map.Entry<String, Object> m : rb.entrySet()) {
                String field = m.getKey();
                if (ignoreField(field)) continue;
                if ((field.equalsIgnoreCase("whr")) || (field.equalsIgnoreCase("where"))) continue;
                fields.put(field, "#{requestBody." + field + "}");
                //fields.put(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field), "#{requestBody." + field + "}");
            }
            if (rb.containsKey("conditions")) {
                where = (String) rb.get("conditions");
                // where = ((String) rb.get("conditions")).replaceAll("#\\{", "#{requestBody.");
            }
        } else if (map.containsKey("entity")) {
            Object entity = map.get("entity");
            getFields(fields,entity);
            where = "uuid = #{entity.uuid}";
        }

        String updateSql = "";
        if (!StringUtils.isEmpty(where)) {
            SQL sql = new SQL().UPDATE(tableName);
            for (Map.Entry<String, String> m : fields.entrySet()) {
                sql.SET(m.getKey() + " = " + m.getValue());
            }
            sql.WHERE(where);
            updateSql = sql.toString().replaceAll("[\r\n]", " ");
            logger.info(updateSql);
        } else {
            map.put("error", "deleteSql: 删除数据时必须提供删除条件!");
            logger.error("deleteSql: 删除数据时必须提供删除条件!");
        }

        return updateSql;
    }

    /**
     * 用于生成 DELETE SQL语句
     * 
     * @param map 最少包含两个参数：tableName, whr-uuid(也可以是其他的条件,如果没有则返回空字符串）
     * @return DELETE SQL 语句，当没有相应的条件时，返回空字符串
     */
    public String deleteSql(Map<String, Object> map) {
        String tableName = (String) map.get("tableName");
        transParamsMap(map);

        String deleteSql = "";
        if (map.containsKey("conditions")) {
            SQL sql = new SQL().DELETE_FROM(tableName);
            sql.WHERE((String) map.get("conditions"));
            deleteSql = sql.toString().replaceAll("[\r\n]", " ");
            logger.info(deleteSql);
        } else {
            map.put("error", "deleteSql: 删除数据时必须提供删除条件!");
            logger.error("deleteSql: 删除数据时必须提供删除条件!");
        }

        return deleteSql;
    }

    @SuppressWarnings("unchecked")
    public String batchInsertEntities(Map<String, Object> map) {
        List<Object> list = (List<Object>) map.get("list");
        if (list.size() <= 0) return "";

        String tableName = (String) map.get("tableName");
        if (tableName == null || "".equals(tableName.trim())) return "";

        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ").append(tableName).append(" (");
        Object entity = list.get(0);
        Field[] fields = entity.getClass().getDeclaredFields();
        StringBuilder columns = new StringBuilder();
        StringBuilder valTpl = new StringBuilder();
        for (Field field : fields) {
            field.setAccessible(true);
            String fieldName = field.getName();
            String camelName = fieldName; // CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName);
            columns.append(columns.length() > 0 ? ", " : "").append(camelName);
            valTpl.append(valTpl.length() > 0 ? ", " : "").append("#'{'list[{0}].").append(fieldName).append("}");
        }
        sb.append(columns).append(") VALUES ");
        MessageFormat mf = new MessageFormat("(" + valTpl + ")");
        for (int i = 0, size = list.size(); i < size; i++) {
            sb.append(mf.format(new Object[] { i }));
            sb.append(i < size - 1 ? ", " : "");
        }

        String insertSql = sb.toString().replaceAll("[\r\n]", " ");
        logger.info(insertSql);
        return insertSql;
    }

    @SuppressWarnings("unchecked")
    public String batchInsertUpdateEntities(Map<String, Object> map) {
        List<Object> list = (List<Object>) map.get("list");
        if (list.size() <= 0) return "";

        String tableName = (String) map.get("tableName");
        if (tableName == null || "".equals(tableName.trim())) return "";

        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ").append(tableName).append(" (");

        Object entity = list.get(0);
        Field[] fields = entity.getClass().getDeclaredFields();
        StringBuilder columns = new StringBuilder();
        StringBuilder valTpl = new StringBuilder();
        StringBuilder updates = new StringBuilder();
        for (Field field : fields) {
            field.setAccessible(true);
            String fieldName = field.getName();
            String camelName = fieldName; // CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName);
            try {
                if (null != field.get(entity)) {
                    columns.append(columns.length() > 0 ? ", " : "").append(camelName);
                    valTpl.append(valTpl.length() > 0 ? ", " : "").append("#'{'list[{0}].").append(fieldName).append("}");
                    updates.append(updates.length() > 0 ? ", " : "").append(camelName).append(" = values(").append(camelName).append(")");
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        sb.append(columns).append(") VALUES ");
        MessageFormat mf = new MessageFormat("(" + valTpl + ")");
        for (int i = 0, size = list.size(); i < size; i++) {
            sb.append(mf.format(new Object[] { i }));
            sb.append(i < size - 1 ? ", " : "");
        }
        sb.append(" ON DUPLICATE KEY UPDATE ").append(updates);

        logger.info(sb.toString());

        return sb.toString();
    }

    @SuppressWarnings("unchecked")
    public String batchInsertUpdateMaps(Map<String, Object> map) {
        List<Object> list = (List<Object>) map.get("list");
        if (list.size() <= 0) return "";

        String tableName = (String) map.get("tableName");
        if (tableName == null || "".equals(tableName.trim())) return "";

        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ").append(tableName).append(" (");
        StringBuilder columns = new StringBuilder();
        StringBuilder valTpl = new StringBuilder();
        StringBuilder updates = new StringBuilder();

        Map<String, Object> mapObject = (Map<String, Object>) list.get(0);
        for (Map.Entry<String, Object> me : mapObject.entrySet()) {
            String key = me.getKey();
            Object value = me.getValue();
            if (value != null && !ignoreField(key) && !isCondition(key)) {
                String camelName = key; // CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, key);
                columns.append(columns.length() > 0 ? ", " : "").append(camelName);
                valTpl.append(valTpl.length() > 0 ? ", " : "").append("#'{'list[{0}].").append(key).append("}");
                updates.append(updates.length() > 0 ? ", " : "").append(camelName).append(" = values(")
                        .append(camelName).append(")");
            }
        }

        sb.append(columns).append(") VALUES ");
        MessageFormat mf = new MessageFormat("(" + valTpl + ")");
        for (int i = 0, size = list.size(); i < size; i++) {
            sb.append(mf.format(new Object[] { i }));
            sb.append(i < size - 1 ? ", " : "");
        }
        sb.append(" ON DUPLICATE KEY UPDATE ").append(updates);
        logger.info(sb.toString());

        return sb.toString();
    }

    private final static int PAGESIZE = 15;
    private void transParamsMap(Map<String, Object> map) {
        String limit = "";
        String where = "";
        int size = map.containsKey("size") ? Integer.valueOf((String) map.get("size")) : PAGESIZE;
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            if ("orderBy".equalsIgnoreCase(key)) {
                map.replace(key, orderBy((String) value));
            } else if ("page".equals(key)) {
                Integer page = Integer.valueOf((String) map.get("page"));
                size = size == 0 ? PAGESIZE : size;
                limit = limit(page, size);
                if (map.containsKey("limit")) {
                    map.replace("limit", limit);
                }
            } else if (key.equalsIgnoreCase("whr") || key.equalsIgnoreCase("where")) {
                where = (StringUtils.isEmpty(where.trim()) ? "" : " AND ") + "(" + whr((String) value) + ")";
                logger.debug(where);
            }
        }
        if (!StringUtils.isEmpty(limit)) {
            if (!map.containsKey("size")) map.put("size", String.valueOf(size));
            map.put("limit", limit);
        }
        if (map.containsKey("conditions")) {
            map.replace("conditions", where);
        } else {
            map.put("conditions", where);
        }
    }

    // 生成where子句
    private String whr(String exp) {
        return exp.replaceAll(",", " AND ")
                .replaceAll(";", " OR ")
                .replaceAll("-([^\\s,;，；]+)", " = '$1'")
                .replaceAll("~([^\\s,;，；]+)", " != '$1'")
                .replaceAll("\\*([^\\s,;，；]+)", " LIKE '%$1%'")
                .replaceAll("\\^([^\\s,;，；]+)", " NOT LIKE '%$1%'");
    }

    // 生成排序子句
    private String orderBy(String order) {
        if (!StringUtils.isEmpty(order)) {
            String clause = order.replaceAll("\\+", "").replaceAll("-", " DESC");
            return clause;
        } else {
            return "";
        }
    }

    // 适用于mysql,生成limit子句
    private String limit(Integer page, Integer size) {
        return "limit " + ((page == 0 ? 1 : page) - 1) * size + ", " + size;
    }

    private boolean ignoreField(String fieldName) {
        return (("apiPath,;queryString,;tableName,;uri,;alias,;orderBy,;limit,;having,;groupBy,;conditions,;orderby")
                .contains(fieldName) || fieldName.startsWith("op-") || fieldName.startsWith("op_")
                || fieldName.startsWith("btn-") || fieldName.startsWith("btn_"));
    }

    private boolean isCondition(String fieldName) {
        return (fieldName.equalsIgnoreCase("where") || fieldName.equalsIgnoreCase("whr"));
    }
}